package com.geek688.auxiliarytool.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * excel操作类
 * @author jack
 *
 */
public class ExcelUtil {

	/**
	 * 创建excel文档并写入outStream输出流中
	 * @param outStream
	 * @param mainTitle
	 * @param titles
	 * @param contents
	 */
	public final static void buildExcel(OutputStream outStream,
			String mainTitle, String[] titles, List<String[]> contents) {
		int beginRow = 0;
		try {
			/** **********创建工作簿************ */
			WritableWorkbook workbook = Workbook.createWorkbook(outStream);

			/** **********创建工作表************ */
			WritableSheet sheet = workbook.createSheet("Sheet1", 0);

			/** **********设置纵横打印（默认为纵打）、打印纸***************** */
			jxl.SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);

			/** ************设置单元格字体************** */
			WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
			WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);

			/** ************以下设置三种单元格样式************ */
			// 用于标题居中
			WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
			wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_center.setWrap(false); // 文字是否换行

			// 用于正文居左
			WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
			wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_left.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_left.setWrap(false); // 文字是否换行

			// excel大标题
			if (mainTitle != null && !mainTitle.equals("")) {
				sheet.mergeCells(0, 0, titles.length - 1, 0);
				sheet.addCell(new Label(0, 0, mainTitle, wcf_center));
				beginRow = beginRow + 1;
			}

			/** ***************以下是EXCEL第一行列标题********************* */
			if (titles != null && titles.length > 1) {
				for (int i = 0; i < titles.length; i++) {
					sheet.addCell(new Label(i, beginRow, titles[i], wcf_center));
				}
				beginRow = beginRow + 1;
			}
			/** ***************以下是EXCEL正文数据********************* */
			for (int i = 0; i < contents.size(); i++) {// row
				String[] rowContent = contents.get(i);
				for (int j = 0; j < titles.length; j++) { // cell
					String content = "";
					if (j < rowContent.length) {
						content = rowContent[j];
					}
					sheet.addCell(new Label(j, i + beginRow, content, wcf_left));
				}
			}
			workbook.write();
			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 创建excel文档并写入outStream输出流中
	 * @param outStream
	 * @param mainTitle
	 * @param titles
	 * @param contents
	 * @throws IOException 
	 */
	public final static void buildExcelNew(OutputStream outStream,
			String mainTitle, String[] titles, List<List<Object>> contents) throws IOException {
		int beginRow = 0;
		try {
			/** **********创建工作簿************ */
			WritableWorkbook workbook = Workbook.createWorkbook(outStream);

			/** **********创建工作表************ */
			WritableSheet sheet = workbook.createSheet("Sheet1", 0);

			/** **********设置纵横打印（默认为纵打）、打印纸***************** */
			jxl.SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);

			/** ************设置单元格字体************** */
			WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
			WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);

			/** ************以下设置三种单元格样式************ */
			// 用于标题居中
			WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
			wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_center.setWrap(false); // 文字是否换行

			// 用于正文居左
			WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
			wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_left.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_left.setWrap(false); // 文字是否换行

			// excel大标题
			if (mainTitle != null && !mainTitle.equals("")) {
				sheet.mergeCells(0, 0, titles.length - 1, 0);
				sheet.addCell(new Label(0, 0, mainTitle, wcf_center));
				beginRow = beginRow + 1;
			}

			/** ***************以下是EXCEL第一行列标题********************* */
			if (titles != null && titles.length > 1) {
				for (int i = 0; i < titles.length; i++) {
					sheet.addCell(new Label(i, beginRow, titles[i], wcf_center));
				}
				beginRow = beginRow + 1;
			}
			/** ***************以下是EXCEL正文数据********************* */
			for (int row = 0; row < contents.size(); row++) {// row
				List<Object> rowContent = contents.get(row);
				for (int col = 0; col < titles.length; col++) { // cell
					String content = "";
					BigDecimal number = new BigDecimal(0);
					Date date ;
					Integer in = 0;
					if (col < rowContent.size()) {
						if(rowContent.get(col) instanceof String ){
							content = (String) rowContent.get(col);
							sheet.addCell(new Label(col, row + beginRow, content, wcf_left));
						}else if(rowContent.get(col) instanceof BigDecimal){
							number = (BigDecimal) rowContent.get(col);
							sheet.addCell(new Number(col, row + beginRow, number.doubleValue()));
						}else if(rowContent.get(col) instanceof Date){
							date = (Date) rowContent.get(col);
							sheet.addCell(new Label(col, row + beginRow, DateUtils.formatDate("yyyy-MM-dd HH:mm:ss", date),wcf_center));
						}else if(rowContent.get(col) == null){
							sheet.addCell(new Label(col, row + beginRow,"",wcf_center));
						}else{
							in = (Integer) rowContent.get(col);
							sheet.addCell(new Number(col, row + beginRow, in, wcf_left));
						}
						
					}
					//sheet.addCell(new Label(j, i + beginRow, content, wcf_left));
				}
			}
			workbook.write();
			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (outStream != null) {
				outStream.close();
			}
		}
	}
	/**
	 * 创建excel文档并写入outStream输出流中---如果超过excle表的数据，则在建立一个sheet
	 * @param outStream
	 * @param mainTitle
	 * @param titles
	 * @param contents
	 */
	public final static void buildExcelMore(OutputStream outStream,
			String mainTitle, String[] titles, List<String[]> contents) {
		try {
			String sheetName="sheet";
			/** **********创建工作簿************ */
			WritableWorkbook workbook = Workbook.createWorkbook(outStream);
			
			/** ***************以下是EXCEL正文数据********************* */
			if (contents.size() > 65000) {
				int k = (contents.size() + 65000) / 65000;//2
	            for (int i = 1; i <= k; i++) {
	                if (i < k) {
	    				buildOneExcel(workbook ,sheetName+i,i-1,outStream,
	    						mainTitle, titles,contents.subList((i - 1) * 65000, i * 65000));
	                } else {
	    				buildOneExcel(workbook ,sheetName+i,i-1,outStream,
	    						mainTitle, titles,contents.subList((i - 1) * 65000, contents.size()));	
	                }
	            }
			}else{
				buildOneExcel(workbook ,sheetName+"1",0,outStream,
						mainTitle, titles,contents);
			}

			workbook.write();
			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 创建一个sheet
	 * @param workbook
	 * @param sheetName
	 * @param i  i>=0
	 * @param outStream
	 * @param mainTitle
	 * @param titles
	 * @param contents
	 */
	public final static void buildOneExcel(WritableWorkbook workbook ,String sheetName,int x,OutputStream outStream,
			String mainTitle, String[] titles, List<String[]> contents) {
		int beginRow = 0;
		try {

			/** **********创建工作表************ */
			WritableSheet sheet = workbook.createSheet(sheetName, x);

			/** **********设置纵横打印（默认为纵打）、打印纸***************** */
			jxl.SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);

			/** ************设置单元格字体************** */
			WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
			WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.BOLD);

			/** ************以下设置三种单元格样式************ */
			// 用于标题居中
			WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
			wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_center.setWrap(false); // 文字是否换行

			// 用于正文居左
			WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
			wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_left.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_left.setWrap(false); // 文字是否换行

			// excel大标题
			if (mainTitle != null && !mainTitle.equals("")) {
				sheet.mergeCells(0, 0, titles.length - 1, 0);
				sheet.addCell(new Label(0, 0, mainTitle, wcf_center));
				beginRow = beginRow + 1;
			}

			/** ***************以下是EXCEL第一行列标题********************* */
			if (titles != null && titles.length > 1) {
				for (int i = 0; i < titles.length; i++) {
					sheet.addCell(new Label(i, beginRow, titles[i], wcf_center));
				}
				beginRow = beginRow + 1;
			}
			/** ***************以下是EXCEL正文数据********************* */
			for (int i = 0; i < contents.size(); i++) {// row
				String[] rowContent = contents.get(i);
				for (int j = 0; j < titles.length; j++) { // cell
					String content = "";
					if (j < rowContent.length) {
						content = rowContent[j];
					}
					sheet.addCell(new Label(j, i + beginRow, content, wcf_left));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 导出文件到指定位置
	 * @author hanjiefei
	 * @date 2019年3月20日
	 * @param path 输出路径
	 * @param filename 文件名
	 * @param titles 标题
	 * @param keySet 键值对 (需要排序 必须和标题内容一一对应)
	 * @param list 内容
	 * @throws IOException
	 */
	public static void exportExcel(String path,String filename,String[] titles,String[] keySet,List<Map<String, Object>> data) throws IOException {
		File file = FileUtil.checkFile(path,filename);
		OutputStream outputStream = new FileOutputStream(file);
		List<List<Object>> contens = new ArrayList<List<Object>>();
		for(Map<String, Object> map:data){
			List<Object> tmp = new ArrayList<Object>();
			for(String key:keySet){
				tmp.add(map.get(key));
			}
			contens.add(tmp);
		}
		ExcelUtil.buildExcelNew(outputStream, filename, titles, contens);
	}
}
